import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# 定义列名和字段描述
columns_description = {
    "house_description": "卖点",
    "house_community": "楼盘名称",
    "house_location": "地址",
    "house_rooms": "房间数",
    "house_square": "面积",
    "house_building_type": "建筑类型",
    "house_built_date": "建造年代",
    "house_unit_price": "单价"
}

# 加载数据集，指定列名
data = pd.read_csv('anjuke.csv', encoding='gbk', header=None, names=columns_description.keys())

# 查看数据分布情况
print(data.describe())

# 打印所有列名，检查列名是否正确
print("列名:", data.columns)

# 清理列名，去除空格和特殊字符
data.columns = [col.strip().replace('¥', '').replace('㎡', '').replace('元/', '') for col in data.columns]

# 打印清理后的列名，确保列名正确
print("清理后列名:", data.columns)
data['house_square'] = data['house_square'].astype(str).str.replace('㎡', '').str.strip()
data['house_unit_price'] = data['house_unit_price'].astype(str).str.replace('元/㎡', '').str.strip()
# data['house_total_price'] = data['house_total_price'].astype(str).str.replace('元', '').str.strip()

# 将相关列转换为数值类型
data['house_square'] = pd.to_numeric(data['house_square'], errors='coerce')
data['house_unit_price'] = pd.to_numeric(data['house_unit_price'], errors='coerce')
# data['house_total_price'] = pd.to_numeric(data['house_total_price'], errors='coerce')

# 检查转换后的数据
print(data[['house_square', 'house_unit_price']].describe())

# 定义一个函数来检测和处理异常值
def detect_and_handle_outliers(df, column):
    # 计算Q1（25%分位数）和Q3（75%分位数）
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    # 计算IQR
    IQR = Q3 - Q1
    # 定义异常值的范围
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    # 识别异常值
    outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]
    # 如果存在异常值，打印出来并处理
    if not outliers.empty:
        print(f"Detected outliers in {column}:")
        print(outliers)
        # 这里我们选择用平均值替换异常值，也可以选择删除异常值
        mean_val = df[column].mean()
        df.loc[(df[column] < lower_bound) | (df[column] > upper_bound), column] = mean_val

# 处理面积字段中的异常值
detect_and_handle_outliers(data, 'house_square')

# 处理单价字段中的异常值
detect_and_handle_outliers(data, 'house_unit_price')

# 处理总价字段中的异常值
# detect_and_handle_outliers(data, 'house_total_price')

# 检查处理后的数据
print(data[['house_square', 'house_unit_price']].describe())

# 绘制单价的直方图
plt.figure(figsize=(10, 6))
sns.histplot(data['house_unit_price'].dropna(), bins=30, kde=True)
plt.title('House Unit Price Distribution')
plt.xlabel('Unit Price ')
plt.ylabel('Frequency')
plt.show()

# 保存处理后的数据集
data.to_csv('chongqing_ershou_house_cleaned.csv', index=False, encoding='utf-8')